﻿''' <summary>
''' 用户管理的账套数据操作类clsUserLedgerDbAccess
''' 对应表S_UserLedger
''' Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\自写系统\rjcwxt\Rjcwxt\data\sys.mdb;Persist Security Info=True
''' </summary>
Public Class ClsUserLedgerDbAccess
    Private mConnectionString As String '数据库连接字符串
    Private mUserLedgerTableName As String 'S_User在库中的表名 S_UserLedger

    Public Sub New(ByVal connString As String)
        mConnectionString = connString
    End Sub

    Public Sub New(ByVal connString As String, ByVal tableName As String)
        mConnectionString = connString
        mUserLedgerTableName = tableName

    End Sub

    Public Property ConnectionString As String
        Get
            Return mConnectionString
        End Get
        Set(value As String)
            mConnectionString = value
        End Set
    End Property

    Public Property UserLedgerTableName As String
        Get
            Return mUserLedgerTableName
        End Get
        Set(value As String)
            mUserLedgerTableName = value
        End Set
    End Property

    ''' <summary>
    ''' 删除指定账套相关的用户记录：ClsUserLedger
    ''' </summary>
    ''' <param name="lId">账套编号</param>
    ''' <returns>非0成功，0值失败</returns>
    Public Function DeleteByLedgerId(ByVal lId As Integer) As Int32

        Try
            Dim tmpSql As String
            Dim tmpDbA As ClsDbAccessMdb = New ClsDbAccessMdb(mConnectionString)
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpRet As Int32

            '打开数据库
            tmpDbA.DbOpen()

            tmpSql = "Delete From [" & TABLE_NAME_USERLEDGER & "] "
            tmpSql &= " where [LedgerId]=@LedgerId "


            tmpParas(0) = New OleDb.OleDbParameter("@LedgerId", OleDb.OleDbType.Integer)

            tmpParas(0).Value = lId


            tmpRet = tmpDbA.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbA.DbClose()

            Return tmpRet

        Catch ex As OleDb.OleDbException
            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserLedgerDbAccess - InsertUserLedger"

            Return 0
        End Try

    End Function

    ''' <summary>
    ''' 插入一条新的用户与账套关系记录
    ''' </summary>
    ''' <param name="ul">类ClsUserLedger对象</param>
    ''' <returns>非0值成功</returns>
    Public Function InsertUserLedger(ByVal ul As ClsUserLedger) As Int32
        Try
            Dim tmpSql As String
            Dim tmpDbA As ClsDbAccessMdb = New ClsDbAccessMdb(mConnectionString)
            Dim tmpParas(7) As OleDb.OleDbParameter
            Dim tmpRet As Int32

            '打开数据库
            tmpDbA.DbOpen()

            tmpSql = "Insert Into [" & TABLE_NAME_USERLEDGER & "] ("
            tmpSql &= "[UserId],[LedgerId],[UserRightVoucher],[UserRightLedger],"
            tmpSql &= "[UserRightReport],[UserRightFinancialSetup],[UserRightSysSetup],[UserRightSysManage]"
            tmpSql &= ") values ("
            tmpSql &= "@UserId,@LedgerId,@UserRightVoucher,@UserRightLedger,"
            tmpSql &= "@UserRightReport,@UserRightFinancialSetup,@UserRightSysSetup,@UserRightSysManage"
            tmpSql &= ")"

            tmpParas(0) = New OleDb.OleDbParameter("@UserId", OleDb.OleDbType.Integer)
            tmpParas(1) = New OleDb.OleDbParameter("@LedgerId", OleDb.OleDbType.Integer)
            tmpParas(2) = New OleDb.OleDbParameter("@UserRightVoucher", OleDb.OleDbType.Boolean)
            tmpParas(3) = New OleDb.OleDbParameter("@UserRightLedger", OleDb.OleDbType.Boolean)
            tmpParas(4) = New OleDb.OleDbParameter("@UserRightReport", OleDb.OleDbType.Boolean)
            tmpParas(5) = New OleDb.OleDbParameter("@UserRightFinancialSetup", OleDb.OleDbType.Boolean)
            tmpParas(6) = New OleDb.OleDbParameter("@UserRightSysSetup", OleDb.OleDbType.Boolean)
            tmpParas(7) = New OleDb.OleDbParameter("@UserRightSysManage", OleDb.OleDbType.Boolean)

            tmpParas(0).Value = ul.UserId
            tmpParas(1).Value = ul.LedgerId
            tmpParas(2).Value = ul.UserRightVoucher
            tmpParas(3).Value = ul.UserRightLedger
            tmpParas(4).Value = ul.UserRightReport
            tmpParas(5).Value = ul.UserRightFinancialSetup
            tmpParas(6).Value = ul.UserRightSysSetup
            tmpParas(7).Value = ul.UserRightSysManage

            tmpRet = tmpDbA.InsertData(tmpSql, tmpParas, True)

            '关闭数据库
            tmpDbA.DbClose()

            Return tmpRet

        Catch ex As OleDb.OleDbException
            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserLedgerDbAccess - InsertUserLedger"

            Return 0
        End Try
    End Function

    ''' <summary>
    ''' 根据指定的用户编号 和 账套编号 返回 用户账套 集合
    ''' </summary>
    ''' <param name="userId">用户编号 </param>
    ''' <param name="ledgerId">账套编号 </param>
    ''' <returns>ClsUserLedger类集合</returns>
    Public Function GetUserLederByUserIdAndLedgerId(ByVal userId As Integer, ByVal ledgerId As Integer) As List(Of ClsUserLedger)
        Try

            Dim tmpSql As String
            Dim tmpDbAccess As ClsDbAccessMdb = New ClsDbAccessMdb(mConnectionString)
            Dim tmpList As List(Of ClsUserLedger)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & TABLE_NAME_USERLEDGER & "] where [UserId]=" & userId & " and [LedgerId] = " & ledgerId

            '查询并将返回数据解析为List对象
            tmpList = ConvertRecordToUserLedgerClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpList

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserLedgerDbAccess - GetLedgerByUserId"

            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' 取出指定账套的全部管理用户
    ''' </summary>
    ''' <param name="lId">账套编号 </param>
    ''' <returns>用户账套关系集合</returns>
    Public Function GetUsersByLedgerId(ByVal lId As Integer) As List(Of ClsUserLedger)
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = New ClsDbAccessMdb(mConnectionString)
            Dim tmpList As List(Of ClsUserLedger)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & TABLE_NAME_USERLEDGER & "] where [LedgerId]=" & lId

            '查询并将返回数据解析为List对象
            tmpList = ConvertRecordToUserLedgerClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpList

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserLedgerDbAccess - GetUsersByLedgerId"

            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' 取出指定用户的全部账套相关信息
    ''' </summary>
    ''' <param name="userId"></param>
    ''' <returns></returns>
    Public Function GetUserLedgerByUserId(ByVal userId As Integer) As List(Of ClsUserLedger)
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = New ClsDbAccessMdb(mConnectionString)
            Dim tmpList As List(Of ClsUserLedger)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & TABLE_NAME_USERLEDGER & "] where [UserId]=" & userId

            '查询并将返回数据解析为List对象
            tmpList = ConvertRecordToUserLedgerClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpList

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserLedgerDbAccess - GetLedgerByUserId"

            Return Nothing
        End Try
    End Function

    Public Function ConvertRecordToUserLedgerClass(ByVal reader As OleDb.OleDbDataReader) As List(Of ClsUserLedger)
        'S_UserLedger
        'UserLedgerId, UserId, LedgerId, UserRightVoucher,UserRightLedger,UserRightReport,UserRightFinancialSetup,UserRightSysSetup,UserRightSysManage
        If reader Is Nothing Then
            Return Nothing
        End If

        Try

            Dim tmpUserList As List(Of ClsUserLedger) = New List(Of ClsUserLedger)
            Dim tmpCls As ClsUserLedger

            While reader.Read()

                tmpCls = New ClsUserLedger()

                With tmpCls
                    .UserLedgerId = reader.GetInt32(0)
                    .UserId = reader.GetInt32(1)
                    .LedgerId = reader.GetInt32(2)
                    .UserRightVoucher = reader.GetBoolean(3)
                    .UserRightLedger = reader.GetBoolean(4)
                    .UserRightReport = reader.GetBoolean(5)
                    .UserRightFinancialSetup = reader.GetBoolean(6)
                    .UserRightSysSetup = reader.GetBoolean(7)
                    .UserRightSysManage = reader.GetBoolean(8)

                End With

                tmpUserList.Add(tmpCls)

            End While

            Return tmpUserList

        Catch ex As Exception

            mErrorNumber = -1001
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserLedgerDbAccess - ConvertRecordToUserLedgerClass"

            Return Nothing
        End Try

    End Function


End Class